package org.firefly.db.dialect;

import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.firefly.db.meta.ColumnMeta;
import org.firefly.db.meta.TableMeta;

/**
 * Oracle 数据库方言
 * @author firefly
 *
 */
public class OracleDialect extends DialectImpl {
//	public OracleDialect(){}
//	public OracleDialect(Connection c,String schema) throws SQLException{
//		super(c,schema);
//		if(schema==null || "".equals(schema)){
//			throw new RuntimeException("oracle 必须提供schema。");
//		}
//	}
//	public OracleDialect(){}
//	public OracleDialect(Connection c,String schema) throws SQLException{
//		super(c,schema);
//		if(schema==null || "".equals(schema)){
//			throw new RuntimeException("oracle 必须提供schema。");
//		}
//	}
//	private static Map<String,Class<?>> types = new HashMap<String,Class<?>>();
	private static Map<Integer,String> types = new HashMap<Integer,String>();
	
	static{
		types.put(Types.CHAR, "char");
		types.put(Types.VARCHAR, "varchar2");
		types.put(Types.NUMERIC, "number");
		types.put(Types.DECIMAL, "number");
		types.put(Types.FLOAT, "float");
		types.put(Types.DOUBLE, "number");
		types.put(Types.INTEGER, "integer");
		types.put(Types.CLOB, "clob");
		types.put(Types.BLOB, "blob");
		types.put(Types.VARBINARY, "long");
		types.put(Types.BIT, "number");
		types.put(Types.DATE, "date");
		types.put(Types.BINARY, "timestamp");
		types.put(Types.TIMESTAMP, "date");
		
		types.put(Types.TINYINT, "number");
		types.put(Types.LONGVARCHAR, "clob");
		types.put(Types.LONGVARBINARY, "blob"); //image -4
		
	}
//	static{
//		types.put("varchar2", String.class);
//		types.put("number", Number.class);
//		types.put("int", Integer.class);
//		types.put("float", Float.class);
//		types.put("clob", Clob.class);
//		types.put("blob", Blob.class);
//		types.put("long", Long.class);
//		types.put("date", Date.class);
//	}
	
//	final String getTableMetasSQL = "select table_name from user_tables";
//	@Override
//	public List<TableMeta> getTableMetas() throws SQLException{
//		List<TableMeta> ls = new ArrayList<TableMeta>(); 
//		Statement s = c.createStatement();
//		ResultSet rs = s.executeQuery(getTableMetasSQL);
//		try{
//			while(rs.next()){
//				String tableName = rs.getString("table_name");
//				List<ColumnMeta> colMetas = getColumnMetas(tableName);
//				TableMeta tm = new TableMeta();
//				tm.setName(tableName);
//				tm.setColumnMetaLs(colMetas);
//			}
//		}finally{
//			rs.close();
//			s.close();
//		}
//		return ls;
//	}
	
//	final String getColumnMetasSQL = "select column_name,data_type,char_length,nullable from user_tab_columns where Table_Name=?";
//	@Override 
//	public List<ColumnMeta> getColumnMetas(String tableName) throws SQLException{
//		List<ColumnMeta> ls = new ArrayList<ColumnMeta>(); 
//		PreparedStatement ps = c.prepareStatement(getColumnMetasSQL);
//		ps.setString(1, tableName);
//		ResultSet rs = ps.executeQuery();
//		try{
//			while(rs.next()){
//				String columnName = rs.getString("column_name");
//				String sqlType = rs.getString("data_type");
//				int length = rs.getInt("char_length");
//				String nullable = rs.getString("nullable");
//				Class<?> javaType = sqlType2JavaType(sqlType);
//
//				ColumnMeta cm = new ColumnMeta();
//				cm.setColumnName(columnName);
//				cm.setTableName(tableName);
//				cm.setJavaType(sqlType2JavaType(sqlType));
//				if(columnTypeHasLength(javaType)){
//					cm.setLength(length);
//				}
//				cm.setNullAble("Y".equals(nullable));
//				ls.add(cm);
//			}
//		}finally{
//			rs.close();
//			ps.close();
//		}
//		return ls;
//	}
//	
//	@Override
//	public String javaType2SQLType(Class<?> javaType){
//		Iterator<Entry<String, Class<?>>> it = types.entrySet().iterator();
//		String type =null;
//		while (it.hasNext()) {
//			Map.Entry<String,Class<?>> entry = it.next();
//			Object obj = entry.getValue();
//			if (javaType.equals(obj)) {
//				type = entry.getKey();
//				break;
//			}
//		}
//		if(type==null){
//			System.err.println("查无此类型");
//		}
//		return type;
//	}
//	
//	@Override
//	public Class<?> sqlType2JavaType(String sqlType){
//		Class<?> type = types.get(sqlType);;
//		if(type==null){
//			System.err.println("查无此类型");
//		}
//		return type;
//	}

	
	@Override
	public String processSQLVal(Object val,int javaType, ColumnMeta cm){
		String rval = null;
	    if(val != null){
		    try {
		        switch(javaType){
		            case Types.CHAR: 
		            case Types.CLOB:
		            case Types.VARCHAR: 
		                rval = processStr(String.valueOf(val));
		                break;
		            case Types.FLOAT: 
		            case Types.NUMERIC: 
		            case Types.DOUBLE: 
		            case Types.DECIMAL: 
		                rval = String.valueOf(val);//BigDecimal.class.cast(val).toString();
		                break;
		            case Types.INTEGER:
		                rval = Integer.class.cast(val).toString();
		                break;
		            case Types.BIT:
		                rval =  Boolean.class.cast(val)?"1":"0";
		                break;
		            case Types.DATE: 
		                if(String.class.isInstance(val)){
		                    rval =  "to_date('"+val.toString()+"','yyyy-mm-dd')";
		                }else if(Date.class.isInstance(val)){
		                    rval =  "to_date('"+dateSDF.format((Date)val)+"','yyyy-mm-dd')";
		                }
		                break;
		            case Types.TIMESTAMP:
		                if(String.class.isInstance(val)){
		                    rval =  "to_date('"+val.toString()+"','yyyy-mm-dd hh24:mi:ss')";
		                }else if(Date.class.isInstance(val)){
		                    rval =  "to_date('"+dateTimeSDF.format((Date)val)+"','yyyy-mm-dd hh24:mi:ss')";
		                }
		                break;
		            case Types.VARBINARY:
		            case Types.BLOB:
		            default:rval = null;
		        }
		    } catch (ClassCastException e) {
		        e.printStackTrace();
		    }
		}
	    if(rval == null){
	        rval = "NULL";
	    }
	    
	    if(cm!= null && !cm.isNullAble() && ("NULL".equals(rval) || "''".equals(rval))){
            rval = "chr(32)";
	    }
		return rval;
	}
//	@Override
//	public String processSQLVal(Object val,Class<?> toType){
//		if(val==null)return "NULL";
//		if(String.class.equals(toType)){
//			return "'"+String.valueOf(val)+"'";
//		}else if(Number.class.equals(toType)){
//			if(Integer.class.isInstance(val)){
//				return Integer.class.cast(val).toString();
//			}else if(Float.class.isInstance(val)){
//				return Float.class.cast(val).toString();
//			}else if(Double.class.isInstance(val)){
//				return Double.class.cast(val).toString();
//			}
//		}else if(Integer.class.equals(toType)){
//			if(Integer.class.isInstance(val)){
//				return Integer.class.cast(val).toString();
//			}
//		}else if(Float.class.equals(toType)){
//			if(Float.class.isInstance(val)){
//				return Float.class.cast(val).toString();
//			}
//		}else if(Clob.class.equals(toType)){
//			return val.toString();
//		}else if(Blob.class.equals(toType)){
//			System.err.println("无法处理");
//			return "NULL";
//		}else if(Long.class.equals(toType)){
//			if(Long.class.isInstance(val)){
//				return Long.class.cast(val).toString();
//			}
//		}else if(Date.class.equals(toType)){
//			if(String.class.isInstance(val)){
//				return "to_date("+val.toString()+",'yyyy-MM-dd HH:mm:ss')";
//			}else if(Date.class.isInstance(val)){
//				return "to_date("+sdf.format((Date)val)+",'yyyy-MM-dd HH:mm:ss')";
//			}
//		}
//		System.out.println("什么类型？");
//		return val.toString();
//	}
	@Override
	public boolean columnTypeHasDecimal(int javaType){
		switch(javaType){
			case Types.DECIMAL: 
			case Types.FLOAT: 
			case Types.DOUBLE: 
			case Types.NUMERIC: return true;
			default:return false;
		}
	}
	
	@Override
	public boolean columnTypeHasLength(int javaType){
	    switch(javaType){
            case Types.CHAR: 
            case Types.VARCHAR: 
            case Types.FLOAT: return true;
            case Types.DECIMAL:
            case Types.DOUBLE:
            case Types.NUMERIC: return true;
            
            case Types.INTEGER: 
            case Types.CLOB:
            case Types.DATE: 
            case Types.TIMESTAMP:
            case Types.VARBINARY:
            case Types.BLOB:
            default:return false;
        }
	}
	@Override
	public boolean columnTypeHasLength(ColumnMeta cm,int javaType){
		switch(javaType){
			case Types.CHAR: 
			case Types.VARCHAR: 
			case Types.FLOAT: return true;
			case Types.DECIMAL:
			case Types.DOUBLE:
			case Types.NUMERIC: return cm.getDecimal()==0 ?  false :  true;
			
			case Types.INTEGER: 
			case Types.CLOB:
			case Types.DATE: 
			case Types.TIMESTAMP:
			case Types.VARBINARY:
			case Types.BLOB:
			default:return false;
		}
	}
	
	
//	@Override
//	public boolean columnTypeHasLength(Class<?> colType){
//		if(String.class.equals(colType)){
//			return true;
//		}else if(Number.class.equals(colType)){
//			return false;
//		}else if(Integer.class.equals(colType)){
//			return false;
//		}else if(Float.class.equals(colType)){
//			return true;
//		}else if(Clob.class.equals(colType)){
//			return false;
//		}else if(Blob.class.equals(colType)){
//			return false;
//		}else if(Long.class.equals(colType)){
//			return false;
//		}else if(Date.class.equals(colType)){
//			return false;
//		}else{
//			System.err.println("不支持的类型:["+colType.getName()+"]");
//			return false;
//		}
//	}
	
	
	
//	@Override
//	public int tableNameMaxLength(){
//		return 30;
//	}
//
//	@Override
//	public String[] keywords(){
//		return new String[]{"as"};
//	}
	@Override
	protected Map<Integer,String> getTypes() {
		return types;
	}
	
	@Override
	public String getNamingChar() {
		return "";
	}
	
	@Override
	public String getConcatStr(String a,String b){
		return a+" || "+b;
	}
    
	@Override
    public int getLength(int javaType, int length){
	    if (Types.VARCHAR == javaType){
	        if (length > 4000) length = 4000;
	    }
        return length;
    }
	
	
	//为clob列提供多次update进行数据插入。
	private static final String COLB_INSERT_SQL="update [tablename] set [colbName] = [colPartVal] where [pkName]=[pkVal]";
	public List<String> buildClobSql(TableMeta targetTM, String columnName, Map<String,Object> val) throws SQLException {
		List<String> colbSQLs = new ArrayList<String>(); 
		String colVal = String.valueOf(val.get(columnName));
		String pkName = targetTM.getPKname();
		Object pkVal = val.get(pkName);
		if(pkName==null || "".equals(pkName.trim())){
			throw new SQLException("没有为表["+targetTM.getName()+"]提供主键列！请补充后再进行数据处理！");
		}
		String colbSQL = COLB_INSERT_SQL.replace("[tablename]", targetTM.getName()).replace("[colbName]", columnName)
					.replace("[pkName]", pkName).replace("[pkVal]", processStr(pkVal.toString()));
		int m = 500;
		for (int k = 0; k < colVal.length() ; k+=m) {  
			int len = colVal.length()-k;
			String partVal = colVal.substring(k, k+(len>m?m:len));
			colbSQLs.add(colbSQL.replace("[colPartVal]", getConcatStr(columnName, processStr(partVal))));
		}
		return colbSQLs;
	}
	@Override
	public String getPaginationSql(String selectSql,
			String tablename,
			String tAlias,
			String pk,
			String fromTableSql,
			String orderBySql,String whereSql, int curPageNo, int pageSize) {
		String sql = selectSql + fromTableSql + whereSql + orderBySql;
		return new StringBuffer(
				"SELECT * FROM (SELECT ROWNUM as rowNo,pagination_a.* FROM (")	.append(sql).append(") pagination_a  where ROWNUM <= ")
				.append(pageSize * curPageNo).append(") pagination_b WHERE pagination_b.rowno > ").append(pageSize * (curPageNo - 1)).toString();
	}
	@Override
	public String getModifyColumnKey() {
		return "modify";
	}
}
